package com.example.mybatisdemo.mapper;

import com.example.mybatisdemo.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserMapper {
    @ResultMap("resultMap")
    @Select("select * from userinfo")
    List<UserInfo> getList();

    //参数传递
    @Select("select * from userinfo where delete_flag = #{delete_flag}")
    List<UserInfo> getDeleteFlag(Integer delete_flag);

    //参数传递
    @Select("select * from userinfo where delete_flag = ${delete_flag}")
    List<UserInfo> getDeleteFlag2(Integer delete_flag);

    @Select("select * from userinfo where username = #{username}")
    UserInfo getByName(String username);

    @Select("select * from userinfo where username = '${username}'")
    UserInfo getByName2(String username);
    //传递两个参数
    @Select("select * from userinfo where delete_flag = #{delete_flag} and gender = #{gender}")
    List<UserInfo> getUserInfo(Integer delete_flag,Integer gender);

    @Select("select * from userinfo where delete_flag = #{flag} and gender = #{gender}")
    //使用Param对参数重命名
    //使用阿里云一定要使用@Param注解
    List<UserInfo> getUserInfo1(@Param("delete_flag") Integer delete_flag, Integer gender);

    @Select("select * from userinfo where id = #{id}")
    //只有一个结果返回可以使用集合或者对象接收，多个只能用集合接收
    UserInfo getUserInfo2(@Param("id")Integer id);

    //增、删、改都是返回Integer
    @Options(useGeneratedKeys = true,keyProperty = "id")//从对象中获取自增主键
    @Insert("insert into userinfo (username,password,age,gender) values (#{username},#{password},#{age},#{gender})")
    Integer insert(UserInfo userInfo);

    @Insert("insert into userinfo(username,password,age,gender) values (#{userinfo.username}," +
            "#{userinfo.password},#{userinfo.age},#{userinfo.gender})")
    Integer insert2(@Param("userinfo")UserInfo userInfo);

    @Delete("delete from userinfo where id = #{id}")
    Integer delete(Integer id);

    @Update("update userinfo set password = #{password},age = #{age},gender = #{gender} where id = #{id}")
    Integer update(UserInfo userInfo);

    //起别名
    @Select("select id,username,password,age,gender,phone," +
            "delete_flag as deleteFlag,create_time as createTime,update_time as updateTime" +
            " from userinfo")
    List<UserInfo> selectListinfo();

    //参数映射
    @Results(id = "resultMap",value = {@Result(column = "delete_flag",property =  "deleteFlag"),
              @Result(column = "create_time",property =  "createTime"),
              @Result(column = "update_time",property =  "updateTime")
            })
    @Select("select * from userinfo")
    List<UserInfo> selectListinfo2();

    //驼峰自动转换
    @Select("select * from userinfo")
    List<UserInfo> selectListinfo3();

    //演示sql注入
    @Select("select * from userinfo where username =  #{username}")
    List<UserInfo> selectUserByName(String username);

    //演示sql注入
    @Select("select * from userinfo where username =  '${username}'")
    List<UserInfo> selectUserByName2(String username);

    @Select("select * from userinfo where username = '${username}' and password = '${password}'")
    UserInfo queryByNameAndPassword(String username, String password);

    //排序
    @Select("select * from userinfo order by id ${order}")
    List<UserInfo> queryUserIdByOrder(String order);

    //模糊匹配
    @Select("select * from userinfo where username like '%${username}%'")
    List<UserInfo> queryUserIdByLike(String username);

    @Select("select * from userinfo where username like concat('%',#{username}'%')")
    List<UserInfo> queryUserIdByLike2(String username);
    @Insert("<script>"+
            "insert into userinfo"+
            "<trim prefix='(' suffix=')' suffixOverrides=','>" +
            "  <if test='username != null'>username,</if>" +
            "  <if test='password != null'>password,</if>" +
            "  <if test='age != null'>age,</if>" +
            "  <if test='gender != null'>gender,</if>" +
            "</trim>" +
            "values" +
            "<trim prefix='(' suffix=')' suffixOverrides=','>" +
            "  <if test='username != null'>#{username},</if>" +
            "  <if test='password != null'>#{password},</if>" +
            "  <if test='age != null'>#{age},</if>" +
            "  <if test='gender != null'>#{gender},</if>" +
            "</trim>"+
            ";"+
            "</script>")
    Integer insertByCondition(UserInfo userInfo);
}
